<?php
// +----------------------------------------------------------------------
// | 中联致远服务端框架
// +----------------------------------------------------------------------
// | Copyright (c) 2018 http://www.zhongliansoft.com All rights reserved.
// +----------------------------------------------------------------------
// | Author: tony<tanyu@zhongliansoft.com>
// +----------------------------------------------------------------------

namespace app\Manage\controller;

use app\common\controller\Manage; 
use app\common\model\Balance as BalanceModel;
use app\common\model\Goods as GoodsModel;
use app\common\model\WxDailyRetain as WxDailyRetainModel;
use app\common\model\Order as OrderModel; 
use app\common\model\OrderItems as OrderItemsModel;
use app\common\model\WxDsBill as WxDsBillModel;
use app\common\model\WxFdsBill as WxFdsBillModel;
use Request;

class Report extends Manage
{


    //具体参数意思参见https://www.echartsjs.com/option.html
    private $option = [
        'title' => [
            'text' => '报表'
        ],
        'tooltip' => [
            'trigger' => 'axis'
        ],
        'legend' => [
            'data' => []
        ],
        'grid' => [
            'left' => '3%',
            'right' => '4%',
            'bottom' => '3%',
            'containLabel' => true
        ],
        'toolbox' => [
            'right' => '30',
            'feature' => [
                'saveAsImage' => []
            ]
        ],
        'xAxis'=> [
            'type'=> 'category',
            'name' => '日期',
            'boundaryGap' => false,
            'data' => [],
        ],
        'yAxis' => [
            'type' => 'value',
            'name' => '元'
        ],
        'series' => []
    ];

    public function index()
    {
        return $this->fetch('index');
    }

    // 余额汇总统计
    public function balance(){
        if(Request::isAJAX()){
            $date_re = $this->getDate();
            if(!$date_re['status']){
                return $date_re;
            }
            $this->option['title']['text'] = "资金管理汇总表";
            $legend = [
                'balance_recharge' => '充值',
                'balance_recharge_gift' => '赠送',
                'balance_pay' => '消费',
                'balance_refund' => '退费',
                'balance_manage' => '平台调整', 
            ];
            $this->option['legend']['data'] = array_values($legend);
            $xData = $this->getXdata($date_re['data']);
            if(!$xData['status']){
                return $xData;
            }
            $this->option['xAxis']['data'] = $xData['data'];
            $datas = [];        //保存所有数据

            foreach($legend as $k => $v){
                switch($k){
                    case 'balance_recharge':
                        $where = "
                            and ctime >" .$date_re['data']['start'].  "
                            and ctime <" .$date_re['data']['end'].  "
                            and type = 3 
                        ";
                        $data = $this->balanceMark($k, $date_re['data'],$where,'ctime');
                        break;
                    case 'balance_recharge_gift':
                        $where = "
                            and ctime >" .$date_re['data']['start'].  "
                            and ctime <" .$date_re['data']['end'].  "
                            and type = 3 
                        ";
                        $data = $this->balanceMark($k, $date_re['data'],$where,'ctime');
                        break;
                    case 'balance_pay':
                        $where = "
                            and ctime >" .$date_re['data']['start'].  "
                            and ctime <" .$date_re['data']['end'].  "
                            and type = 1 
                        ";
                        $data = $this->balanceMark($k, $date_re['data'],$where,'ctime');
                        break;
                    case 'balance_refund':
                        $where = "
                            and ctime >" .$date_re['data']['start'].  "
                            and ctime <" .$date_re['data']['end'].  "
                            and type = 2 
                        ";
                        $data = $this->balanceMark($k, $date_re['data'],$where,'ctime');
                        break;
                    case 'balance_manage': 
                        $where = "
                            and ctime >" .$date_re['data']['start'].  "
                            and ctime <" .$date_re['data']['end'].  "
                            and type = 7 
                        ";
                        $data = $this->balanceMark($k, $date_re['data'],$where,'ctime');
                        break;
                }

                if($data['status']){
                    $this->option['series'][] = [
                        'name' => $v,
                        'type' => 'line',
                        'data' => array_column($data['data'],'val')
                    ];
                    //存所有数据
                    $datas[$k] = $data['data']; 
                }else{
                    $this->option['series'][] = [
                        'name' => $v,
                        'type' => 'line',
                        'data' => []
                    ];
                }
            }
            //组装数据列表用于table里使用
            $table = [];
            for($i = 0;$i< $date_re['data']['num'];$i++){
                $item = [];
                $item['x'] = $xData['data'][$i];
                foreach($datas as $k => $v){
                    $item[$k.'_val'] = $v[$i]['val'];
                    $item[$k.'_num'] = $v[$i]['num'];
                }
                $table[] = $item;
            }

            return [
                'status' => true,
                'data'   => [
                    'option' => $this->option,
                    'table' => $table
                ],
                'msg'    => '',
            ];
        }else{
            return $this->fetch();
        }
    }

    // 账单对账
    public function wxreconciliationds (){
        if(Request::isAJAX()){
            return $this->wxRecociliationDsMark();
        }else{
            return $this->fetch();
        }
    }

    // 账单对账
    public function wxreconciliationfds (){
        if(Request::isAJAX()){
            return $this->wxRecociliationFdsMark();
        }else{
            return $this->fetch();
        }
    }

    // 账单明细对账
    public function billDetailDs(){
        if(Request::isAjax())
        {
            $result = [
                'status' => false,
                'data'   => [],
                'msg'    => '',
            ];

            if(!input('param.date')){
                return error_code(10051);
            }

            if(input('param.hour') != ''){
                if(input('param.hour') == 0) {
                    $data['start'] = strtotime(input('param.date'))+60*60*23;
                    $data['end'] = strtotime(input('param.date'))+60*60*24;
                } else {
                    $data['start'] = strtotime(input('param.date'))+60*60*(input('param.hour') - 1);
                    $data['end'] = strtotime(input('param.date'))+60*60*input('param.hour');
                }
            } else { 
                $data['start'] = strtotime(input('param.date'));
                $data['end'] = strtotime(input('param.date'))+60*60*24;
            }
 
            $sql = "select ifnull(a.out_trade_no_, '无') source_id, ifnull(a.jfje_, 0) money, if(isnull(a.out_trade_no_),'无系统订单','系统成功') sysstatus, ifnull(b.order_sn_sh, '无') order_sn_sh, ifnull(b.total_amount, 0) total_amount, ifnull(b.paystatus, '无微信充值订单') paystatus from (select DISTINCT a.out_trade_no_, a.jfje_, a.time_jf_ from zlzhsf.t_pay_order a, zlzhsf.t_pays_order_detail b, zlzhsf.psfxm c  where a.id_ = b.t_pays_order_id_ and b.sfxmdm_ = c.sfxmdm and c.sfxmlxdm in ('09') and is_qrzf_ = 1 and UNIX_TIMESTAMP(time_jf_) > " .$data['start'].  " and UNIX_TIMESTAMP(time_jf_) < " .$data['end']. ") a left join 
                (
                    select a.order_sn_sh, total_amount, '已退费' paystatus from zl_wx_ds_bill a join
                    (select order_sn_sh from zl_wx_ds_bill where pay_status = 'REFUND') b on a.order_sn_sh = b.order_sn_sh
                    where pay_status = 'SUCCESS' and UNIX_TIMESTAMP(pay_time) > " .$data['start'].  " and UNIX_TIMESTAMP(pay_time) < " .$data['end']. "
                    
                    union select a.order_sn_sh, total_amount, '支付成功' from zl_wx_ds_bill a left join
                    (select order_sn_sh from zl_wx_ds_bill where pay_status = 'REFUND') b on a.order_sn_sh = b.order_sn_sh
                    where pay_status = 'SUCCESS' and isnull(b.order_sn_sh) is true and UNIX_TIMESTAMP(pay_time) > " .$data['start'].  " and UNIX_TIMESTAMP(pay_time) < " .$data['end']. "
                ) b on a.out_trade_no_ = b.order_sn_sh 

                union select ifnull(a.out_trade_no_, '无') source_id, ifnull(a.jfje_, 0) money, if(isnull(a.out_trade_no_),'无系统订单','系统成功') sysstatus, ifnull(b.order_sn_sh, '无') order_sn_sh, ifnull(b.total_amount, 0) total_amount, ifnull(b.paystatus, '无微信充值订单') paystatus from (select DISTINCT a.out_trade_no_, a.jfje_, a.time_jf_ from zlzhsf.t_pay_order a, zlzhsf.t_pays_order_detail b, zlzhsf.psfxm c  where a.id_ = b.t_pays_order_id_ and b.sfxmdm_ = c.sfxmdm and c.sfxmlxdm in ('09') and is_qrzf_ = 1 and UNIX_TIMESTAMP(time_jf_) > " .$data['start'].  " and UNIX_TIMESTAMP(time_jf_) < " .$data['end']. ") a right join 
                (
                    select a.order_sn_sh, total_amount, '已退费' paystatus from zl_wx_ds_bill a join
                    (select order_sn_sh from zl_wx_ds_bill where pay_status = 'REFUND') b on a.order_sn_sh = b.order_sn_sh
                    where pay_status = 'SUCCESS' and UNIX_TIMESTAMP(pay_time) > " .$data['start'].  " and UNIX_TIMESTAMP(pay_time) < " .$data['end']. "
                
                    union select a.order_sn_sh, total_amount, '支付成功' from zl_wx_ds_bill a left join 
                    (select order_sn_sh from zl_wx_ds_bill where pay_status = 'REFUND') b on a.order_sn_sh = b.order_sn_sh
                    where pay_status = 'SUCCESS' and isnull(b.order_sn_sh) is true and UNIX_TIMESTAMP(pay_time) > " .$data['start'].  " and UNIX_TIMESTAMP(pay_time) < " .$data['end']. "
                ) b on a.out_trade_no_ = b.order_sn_sh
            ";
    
            $model = new OrderModel();
            $result = $model->query($sql);

            $re['code'] = 0;
            $re['msg'] = '';
            $re['count'] = count($result);
            $re['data'] = $result;

            return $re;
        }else{
            return $this->fetch('bill_detailds',['date' => input('param.date'), 'hour' => input('param.hour')]);
        } 
    }
 
    // 账单明细对账
    public function billDetailFds(){
        if(Request::isAjax())
        {
            $result = [
                'status' => false,
                'data'   => [],
                'msg'    => '',
            ];

            if(!input('param.date')){
                return error_code(10051);
            }

            if(input('param.hour') != ''){
                if(input('param.hour') == 0) {
                    $data['start'] = strtotime(input('param.date'))+60*60*23;
                    $data['end'] = strtotime(input('param.date'))+60*60*24;
                } else {
                    $data['start'] = strtotime(input('param.date'))+60*60*(input('param.hour') - 1);
                    $data['end'] = strtotime(input('param.date'))+60*60*input('param.hour');
                }
            } else { 
                $data['start'] = strtotime(input('param.date'));
                $data['end'] = strtotime(input('param.date'))+60*60*24;
            }
 
            $sql = "select ifnull(a.out_trade_no_, '无') source_id, ifnull(a.jfje_, 0) money, if(isnull(a.out_trade_no_),'无系统订单','系统成功') sysstatus, ifnull(b.order_sn_sh, '无') order_sn_sh, ifnull(b.total_amount, 0) total_amount, ifnull(b.paystatus, '无微信充值订单') paystatus from (select DISTINCT a.out_trade_no_, a.jfje_, a.time_jf_ from zlzhsf.t_pay_order a, zlzhsf.t_pays_order_detail b, zlzhsf.psfxm c  where a.id_ = b.t_pays_order_id_ and b.sfxmdm_ = c.sfxmdm and c.sfxmlxdm in ('01', '02', '03') and is_qrzf_ = 1 and UNIX_TIMESTAMP(time_jf_) > " .$data['start'].  " and UNIX_TIMESTAMP(time_jf_) < " .$data['end']. ") a left join 
                (
                    select a.order_sn_sh, total_amount, '已退费' paystatus from zl_wx_fds_bill a join
                    (select order_sn_sh from zl_wx_fds_bill where pay_status = 'REFUND') b on a.order_sn_sh = b.order_sn_sh
                    where pay_status = 'SUCCESS' and UNIX_TIMESTAMP(pay_time) > " .$data['start'].  " and UNIX_TIMESTAMP(pay_time) < " .$data['end']. "
                    
                    union select a.order_sn_sh, total_amount, '支付成功' from zl_wx_fds_bill a left join
                    (select order_sn_sh from zl_wx_fds_bill where pay_status = 'REFUND') b on a.order_sn_sh = b.order_sn_sh
                    where pay_status = 'SUCCESS' and isnull(b.order_sn_sh) is true and UNIX_TIMESTAMP(pay_time) > " .$data['start'].  " and UNIX_TIMESTAMP(pay_time) < " .$data['end']. "
                ) b on a.out_trade_no_ = b.order_sn_sh 

                union select ifnull(a.out_trade_no_, '无') source_id, ifnull(a.jfje_, 0) money, if(isnull(a.out_trade_no_),'无系统订单','系统成功') sysstatus, ifnull(b.order_sn_sh, '无') order_sn_sh, ifnull(b.total_amount, 0) total_amount, ifnull(b.paystatus, '无微信充值订单') paystatus from (select DISTINCT a.out_trade_no_, a.jfje_, a.time_jf_ from zlzhsf.t_pay_order a, zlzhsf.t_pays_order_detail b, zlzhsf.psfxm c  where a.id_ = b.t_pays_order_id_ and b.sfxmdm_ = c.sfxmdm and c.sfxmlxdm in ('01', '02', '03') and is_qrzf_ = 1 and UNIX_TIMESTAMP(time_jf_) > " .$data['start'].  " and UNIX_TIMESTAMP(time_jf_) < " .$data['end']. ") a right join 
                (
                    select a.order_sn_sh, total_amount, '已退费' paystatus from zl_wx_fds_bill a join
                    (select order_sn_sh from zl_wx_fds_bill where pay_status = 'REFUND') b on a.order_sn_sh = b.order_sn_sh
                    where pay_status = 'SUCCESS' and UNIX_TIMESTAMP(pay_time) > " .$data['start'].  " and UNIX_TIMESTAMP(pay_time) < " .$data['end']. "
                
                    union select a.order_sn_sh, total_amount, '支付成功' from zl_wx_fds_bill a left join 
                    (select order_sn_sh from zl_wx_fds_bill where pay_status = 'REFUND') b on a.order_sn_sh = b.order_sn_sh
                    where pay_status = 'SUCCESS' and isnull(b.order_sn_sh) is true and UNIX_TIMESTAMP(pay_time) > " .$data['start'].  " and UNIX_TIMESTAMP(pay_time) < " .$data['end']. "
                ) b on a.out_trade_no_ = b.order_sn_sh
            ";
    
            $model = new OrderModel();
            $result = $model->query($sql);

            $re['code'] = 0;
            $re['msg'] = '';
            $re['count'] = count($result);
            $re['data'] = $result;

            return $re;
        }else{
            return $this->fetch('bill_detailfds',['date' => input('param.date'), 'hour' => input('param.hour')]);
        } 
    }

    // 设备收入汇总表
    public function device(){
        if(Request::isAJAX()){
            return $this->deviceMark();
        }else{
            return $this->fetch();
        }
    }

    // 用户支出汇总表
    public function user(){
        if(Request::isAJAX()){
            return $this->userMark();
        }else{
            return $this->fetch();
        }
    }

    public function order(){
        if(Request::isAJAX()){
            $date_re = $this->getDate();
            if(!$date_re['status']){
                return $date_re;
            }
            $this->option['title']['text'] = "订单统计";
            $legend = [
                'order_all' => '全部',
                'order_nopay' => '待付款',
                'order_payed' => '已付款',
               // 'order_ship' => '发货',
               // 'order_refund' =>'售后'
            ];
            $this->option['legend']['data'] = array_values($legend);
            $xData = $this->getXdata($date_re['data']);
            if(!$xData['status']){
                return $xData;
            }
            $this->option['xAxis']['data'] = $xData['data'];
            $datas = [];        //保存所有数据

            foreach($legend as $k => $v){
                switch($k){
                    case 'order_all':
                        $where = "
                            and ctime >" .$date_re['data']['start'].  "
                            and ctime <" .$date_re['data']['end'].  "
                        ";
                        $data = $this->orderMark($date_re['data'],$where,'ctime');
                        break;
                    case 'order_nopay':
                        $where = "
                            and ctime >" .$date_re['data']['start'].  "
                            and ctime <" .$date_re['data']['end'].  "
                            and pay_status = 1
                        ";
                        $data = $this->orderMark($date_re['data'],$where,'ctime');
                        break;
                    case 'order_payed':
                        $where = "
                            and payment_time >" .$date_re['data']['start'].  "
                            and payment_time <" .$date_re['data']['end'].  "
                            and pay_status > 1
                        ";
                        $data = $this->orderMark($date_re['data'],$where,'payment_time');
                        break;
                    case 'order_refund':

                        break;
                }

                if($data['status']){
                    $this->option['series'][] = [
                        'name' => $v,
                        'type' => 'line',
                        'data' => array_column($data['data'],'val')
                    ];
                    //存所有数据
                    $datas[$k] = $data['data']; 
                }else{
                    $this->option['series'][] = [
                        'name' => $v,
                        'type' => 'line',
                        'data' => []
                    ];
                }
            }
            //组装数据列表用于table里使用
            $table = [];
            for($i = 0;$i< $date_re['data']['num'];$i++){
                $item = [];
                $item['x'] = $xData['data'][$i];
                foreach($datas as $k => $v){
                    $item[$k.'_val'] = $v[$i]['val'];
                    $item[$k.'_num'] = $v[$i]['num'];
                }
                $table[] = $item;
            }

            return [
                'status' => true,
                'data'   => [
                    'option' => $this->option,
                    'table' => $table
                ],
                'msg'    => '',
            ];
        }else{
            return $this->fetch();
        }
    }

    //收款单。
    public function payments(){
        if(Request::isAJAX()){
            $date_re = $this->getDate();
            if(!$date_re['status']){
                return $date_re;
            }
            $this->option['title']['text'] = "财务统计";
            $legend = [
                'payments_all' => '收款单',
                'payments_order' => '订单收款',
                'payments_order_refund' => '订单退款',
                'payments_recharge' => '充值',
                'payments_tocash' =>'提现'
            ];
            $this->option['legend']['data'] = array_values($legend);
            $xData = $this->getXdata($date_re['data']);
            if(!$xData['status']){
                return $xData;
            }
            $this->option['xAxis']['data'] = $xData['data'];
            $datas = [];        //保存所有数据

            foreach($legend as $k => $v){
                switch($k){
                    case 'payments_all':
                        $where = "
                            and utime >" .$date_re['data']['start'].  "
                            and utime <" .$date_re['data']['end'].  "
                            and status = 2
                        ";
                        $data = $this->paymentsMark($date_re['data'],$where,'utime');
                        break;
                    case 'payments_order':
                        $where = "
                            and utime >" .$date_re['data']['start'].  "
                            and utime <" .$date_re['data']['end'].  "
                            and status = 2
                            and type = 1
                        ";
                        $data = $this->paymentsMark($date_re['data'],$where,'utime');
                        break;
                    case 'payments_order_refund':
                        $where = "
                            and utime >" .$date_re['data']['start'].  "
                            and utime <" .$date_re['data']['end'].  "
                            and status = 2
                            and type = 1
                        ";
                        $data = $this->refundMark($date_re['data'],$where,'utime');
                        break;
                    case 'payments_recharge':
                        $where = "
                            and utime >" .$date_re['data']['start'].  "
                            and utime <" .$date_re['data']['end'].  "
                            and status = 2
                            and type = 2
                        ";
                        $data = $this->paymentsMark($date_re['data'],$where,'utime');
                        break;
                    case 'payments_tocash':
                        $where = "
                            and utime >" .$date_re['data']['start'].  "
                            and utime <" .$date_re['data']['end'].  "
                            and type = 2
                        ";
                        $data = $this->tocashMark($date_re['data'],$where,'utime');
                        break;

                }

                if($data['status']){
                    $this->option['series'][] = [
                        'name' => $v,
                        'type' => 'line',
                        'data' => array_column($data['data'],'val')
                    ];
                    //存所有数据
                    $datas[$k] = $data['data']; 
                }else{
                    $this->option['series'][] = [
                        'name' => $v,
                        'type' => 'line',
                        'data' => []
                    ];
                }
            }
            //组装数据列表用于table里使用
            $table = [];
            for($i = 0;$i< $date_re['data']['num'];$i++){
                $item = [];
                $item['x'] = $xData['data'][$i];
                foreach($datas as $k => $v){
                    $item[$k.'_val'] = $v[$i]['val'];
                    $item[$k.'_num'] = $v[$i]['num'];
                }
                $table[] = $item;
            }

            return [
                'status' => true,
                'data'   => [
                    'option' => $this->option,
                    'table' => $table
                ],
                'msg'    => '',
            ];
        }else{
            return $this->fetch();
        }
    }

    public function goods(){
        if(Request::isAJAX()){
            $result = [
                'code' => 0,
                'msg' => '',
                'count' => 0,
                'data' => []
            ];
            $page = input('param.page',1);
            $limit = input('param.limit',5000);
            $sort = input('param.thesort','desc');
            $filter = input('param.filter','nums');
            $filter_sed = $filter=='nums'?'amount':'nums';

            if(input('?param.date')){
                $theDate = explode(' 到 ',input('param.date'));
                if(count($theDate) == 2){
                    $start = strtotime($theDate[0]);
                    $end = strtotime($theDate[1])+60*60*24;
                }else{
                    return $result;
                }
            }else{
                //默认今天
                $start = strtotime(date('Y-m-d'));
                $end = $start+60*60*24;
            }
            $sql = "
                from ".config('database.prefix')."order_items oi
                left join `".config('database.prefix')."order` o on oi.order_id = o.order_id
                where o.pay_status <> 1
                and o.payment_time > ".$start."
                and o.payment_time <= ".$end."
                group by oi.sn
                order by sum(oi.".$filter.") ".$sort.",sum(oi.".$filter_sed.") ".$sort."
            ";
            $orderItemModel = new OrderItemsModel();
            $count = $orderItemModel->query("select 1 ".$sql); 
            if(!$count){
               return $result; 
            }
            $result['count'] = count($count);

            $select = "select sum(oi.nums) as nums,sum(oi.amount) as amount,oi.sn,oi.name,oi.image_url,oi.addon ";
            $limit_sql = " limit ".($page-1)*$limit.",".$limit;
            $result['data'] = $orderItemModel->query($select.$sql.$limit_sql); 
            if($sort == "desc"){
                $row = ($page-1)*$limit;
            }else{
                $row = $result['count'] - ($page-1)*$limit + 1;
            }
            foreach($result['data'] as $k => $v){
                if($sort == "desc"){
                    $v['id'] = ++$row;
                }else{
                    $v['id'] = --$row;
                }

                if($limit == 5000){
                    $v['addon'] = "\"".$v['addon']."\"";    //如果是导出的话，这里会含有英文逗号，所以要加上双引号
                }

                $result['data'][$k] = $v; 
            }
            $result['start'] = $start;
            $result['end'] = $end;
            $result['sql'] = $select.$sql.$limit_sql;
            return $result;


        }else{
            return $this->fetch();
        }
    }

    //商品收藏统计
    public function goodsCollection(){
        if(Request::isAJAX()){
            $result = [
                'code' => 0,
                'msg' => '',
                'count' => 0,
                'data' => []
            ];
            $page = input('param.page',1);
            $limit = input('param.limit',5000);
            $sort = input('param.thesort','desc');

            if(input('?param.date')){
                $theDate = explode(' 到 ',input('param.date'));
                if(count($theDate) == 2){
                    $start = strtotime($theDate[0]);
                    $end = strtotime($theDate[1])+60*60*24;
                }else{
                    return $result;
                }
            }else{
                //默认今天
                $start = strtotime(date('Y-m-d'));
                $end = $start+60*60*24;
            }
            $sql = "
                from ".config('database.prefix')."goods_collection gc
                left join `".config('database.prefix')."goods` g on gc.goods_id = g.id
                where gc.ctime > ".$start."
                and gc.ctime <= ".$end."
                group by gc.goods_id
                order by sum(gc.goods_id) ".$sort."
            ";
            $goodsModel = new GoodsModel();
            $count = $goodsModel->query("select 1 ".$sql);
            if(!$count){
                return $result;
            }
            $result['count'] = count($count);

            $select = "select count(gc.goods_id) as nums,gc.goods_id,gc.goods_name,g.image_id";
            $limit_sql = " limit ".($page-1)*$limit.",".$limit;
            $result['data'] = $goodsModel->query($select.$sql.$limit_sql);
            if($sort == "desc"){
                $row = ($page-1)*$limit;
            }else{
                $row = $result['count'] - ($page-1)*$limit + 1;
            }
            foreach($result['data'] as $k => $v){
                if($sort == "desc"){
                    $v['id'] = ++$row;
                }else{
                    $v['id'] = --$row;
                }


                $v['image_url'] = _sImage($v['image_id']);
                $result['data'][$k] = $v;
            }
            $result['start'] = $start;
            $result['end'] = $end;
            $result['sql'] = $select.$sql.$limit_sql;
            return $result;


        }else{
            return $this->fetch();
        }
    }


    //根据时间，返回时间段
    private function getDate(){
        $result = [
            'status' => false,
            'data'   => [],
            'msg'    => '',
        ];
        if(input('?param.date')){
            $theDate = explode(' 到 ',input('param.date'));
            if(count($theDate) == 2){
                $data['start'] = strtotime($theDate[0]);
                $data['end'] = strtotime($theDate[1])+60*60*24;
                $data['section'] = 1;
            }else{
                $result['msg'] = '时间段格式不正确';
                return $result;
            }
        }else{
            return error_code(10000);
        }
        //切片维度，1是小时，2是天，3是周，4是月，5是季度，6是半年，7是年
        if(input('?param.section')){
            $data['section'] = input('param.section');
        }
        //算统计需要的参数
        return $this->getTmp($data);

    }

    //换算前台时间按钮
    public function getDateType(){
        $result = [
            'status' => true,
            'data'   => [],
            'msg'    => '123',
        ];

        if(input('?param.date_type')){

            switch (input('param.date_type')){
                case 1:             //当天
                    $result['data']['start'] = date('Y-m-d');
                    $result['data']['end'] = $result['data']['start'];
                    break;
                case 2:             //昨天
                    $result['data']['start'] = date("Y-m-d",strtotime("-1 day")) ;//strtotime(date('Y-m-d'),strtotime('-1 day'));
                    $result['data']['end'] = $result['data']['start'];
                    break;
                case 3:              //本周
                    $week_num = (date('w') != 0 ? date('w'):7);
                    $week_num--;
                    $result['data']['start'] = date('Y-m-d',time() - $week_num * 60*60*24);
                    $result['data']['end'] = date('Y-m-d');
                    break;
                case 4:              //上周
                    $week_num = (date('w') != 0 ? date('w'):7);
                    $week_num = $week_num + 6;
                    $result['data']['start'] = date('Y-m-d',time() - $week_num * 60*60*24);
                    $result['data']['end'] = date('Y-m-d',time() - ($week_num - 6) * 60*60*24);
                    break;
                case 5:              //本月
                    $result['data']['start'] = date("Y-m-01");
                    $result['data']['end'] = date('Y-m-d');
                    break;
                case 6:              //上月
                    $result['data']['start'] = date("Y-m-01",strtotime('first day of -1 month'));
                    $result['data']['end'] = date('Y-m-d', strtotime(date('Y-m-01') . ' -1 day'));
                    break;
                case 7:              //最近7天
                    $result['data']['start'] = date('Y-m-d',time() - 6 * 60*60*24);
                    $result['data']['end'] = date('Y-m-d');
                    break;
                case 8:              //最近一月
                    $result['data']['start'] = date("Y-m-d",strtotime('-1 month +1 day'));
                    $result['data']['end'] = date('Y-m-d');
                    break;
                case 9:              //最近3月
                    $result['data']['start'] = date("Y-m-d",strtotime('-3 month +1 day'));
                    $result['data']['end'] = date('Y-m-d');
                    break;
                case 10:              //最近6月
                    $result['data']['start'] = date("Y-m-d",strtotime('-6 month +1 day'));
                    $result['data']['end'] = date('Y-m-d');
                    break;
                case 11:              //最近1年
                    $result['data']['start'] = date("Y-m-d",strtotime('-12 month +1 day'));
                    $result['data']['end'] = date('Y-m-d');
                    break;

                default:
                    $result['status'] = false;
                    $result['msg'] = '没有此时间维度';
                    return $result;
            }
            return $result;
        }else{
            return error_code(10000);
        }
    }
 
    //换算前台时间按钮，不包含当天
    public function getDateTypeEx(){
        $result = [
            'status' => true,
            'data'   => [],
            'msg'    => '123',
        ];

        if(input('?param.date_type')){

            switch (input('param.date_type')){
                case 1:             //当天
                    $result['data']['start'] = date('Y-m-d');
                    $result['data']['end'] = $result['data']['start'];
                    break;
                case 2:             //昨天
                    $result['data']['start'] = date("Y-m-d",strtotime("-1 day")) ;//strtotime(date('Y-m-d'),strtotime('-1 day'));
                    $result['data']['end'] = $result['data']['start'];
                    break;
                case 3:              //本周
                    $week_num = (date('w') != 0 ? date('w'):7);
                    $week_num--;
                    $result['data']['start'] = date('Y-m-d',time() - $week_num * 60*60*24);
                    $result['data']['end'] = date('Y-m-d',time() - 60*60*24); 
                    break;
                case 4:              //上周
                    $week_num = (date('w') != 0 ? date('w'):7);
                    $week_num = $week_num + 6;
                    $result['data']['start'] = date('Y-m-d',time() - $week_num * 60*60*24);
                    $result['data']['end'] = date('Y-m-d',time() - ($week_num - 6) * 60*60*24);
                    break;
                case 5:              //本月
                    $result['data']['start'] = date("Y-m-01");
                    $result['data']['end'] = date('Y-m-d',time() - 60*60*24);
                    break;
                case 6:              //上月
                    $result['data']['start'] = date("Y-m-01",strtotime('first day of -1 month'));
                    $result['data']['end'] = date('Y-m-d', strtotime(date('Y-m-01') . ' -1 day'));
                    break;
                case 7:              //最近7天
                    $result['data']['start'] = date('Y-m-d',time() - 7 * 60*60*24);
                    $result['data']['end'] = date('Y-m-d',time() - 60*60*24);
                    break;
                case 8:              //最近一月
                    $result['data']['start'] = date("Y-m-d",strtotime('-1 month'));
                    $result['data']['end'] = date('Y-m-d',time() - 60*60*24);
                    break;
                case 9:              //最近3月
                    $result['data']['start'] = date("Y-m-d",strtotime('-3 month'));
                    $result['data']['end'] = date('Y-m-d',time() - 60*60*24);
                    break;
                case 10:              //最近6月
                    $result['data']['start'] = date("Y-m-d",strtotime('-6 month'));
                    $result['data']['end'] = date('Y-m-d',time() - 60*60*24);
                    break;
                case 11:              //最近1年
                    $result['data']['start'] = date("Y-m-d",strtotime('-12 month'));
                    $result['data']['end'] = date('Y-m-d',time() - 60*60*24);
                    break;

                default:
                    $result['status'] = false;
                    $result['msg'] = '没有此时间维度';
                    return $result;
            }
            
            // 这里要处理
            if($result['data']['end'] < $result['data']['start']) {
                $result['status'] = false;
                $result['msg'] = '没有记录，请尝试其他筛选条件！';
                return $result;
            } 

            return $result;
        }else{
            return error_code(10000);
        }
    }

    // 账单对账
    private function wxRecociliationDsMark(){
        $result = [
            'status' => false,
            'data'   => [],
            'msg'    => '',
        ];
  
        $date_re = $this->getDate();
        if(!$date_re['status']){
            return $date_re;
        }
 
        $xData = $this->getXdata($date_re['data']);
        if(!$xData['status']){
            return $xData;
        }

        $sysStatus = 1;
        $sysList = $this->getSysDsBill($date_re, $sysStatus);
        $wxList = $this->getWxDsBill($date_re, $sysStatus);
        $refundWxList = $this->getRefundWxDsBill($date_re, $sysStatus);
        
        //组装数据列表用于table里使用
        $table = [];
        for($i = 0;$i< $date_re['data']['num'];$i++){
            $item = [];
            $item['x'] = $xData['data'][$i];
            
            $item['sys_num'] = $sysList[$i]['sys_num'];
            $item['sys_val'] = $sysList[$i]['sys_val'];
            $item['wx_num'] = $wxList[$i]['wx_num'];
            $item['wx_val'] = $wxList[$i]['wx_val'];
            $item['refund_wx_num'] = $refundWxList[$i]['wx_num'];
            $item['refund_wx_val'] = $refundWxList[$i]['wx_val'];
            $item['status'] = '失败';
            if(($item['sys_num'] == ($item['wx_num'] - $item['refund_wx_num'])) && ($item['sys_val'] == ($item['wx_val'] - $item['refund_wx_val']))){
                $item['status'] = '成功';
            }

            if((input('param.status') == $item['status']) || input('param.status') == ''){
                $table[] = $item;
            }
        }

        $result['data'] = $table;
        $result['status'] = true;
        return $result;
    }

    // 账单对账
    private function wxRecociliationFdsMark(){
        $result = [
            'status' => false,
            'data'   => [],
            'msg'    => '',
        ];
  
        $date_re = $this->getDate();
        if(!$date_re['status']){
            return $date_re;
        }
 
        $xData = $this->getXdata($date_re['data']);
        if(!$xData['status']){
            return $xData;
        }

        $sysStatus = 1;
        $sysList = $this->getSysFdsBill($date_re, $sysStatus);
        $wxList = $this->getWxFdsBill($date_re, $sysStatus);
        $refundWxList = $this->getRefundWxFdsBill($date_re, $sysStatus);
        
        //组装数据列表用于table里使用
        $table = [];
        for($i = 0;$i< $date_re['data']['num'];$i++){
            $item = [];
            $item['x'] = $xData['data'][$i];
            
            $item['sys_num'] = $sysList[$i]['sys_num'];
            $item['sys_val'] = $sysList[$i]['sys_val'];
            $item['wx_num'] = $wxList[$i]['wx_num'];
            $item['wx_val'] = $wxList[$i]['wx_val'];
            $item['refund_wx_num'] = $refundWxList[$i]['wx_num'];
            $item['refund_wx_val'] = $refundWxList[$i]['wx_val'];
            $item['status'] = '失败';
            if(($item['sys_num'] == ($item['wx_num'] - $item['refund_wx_num'])) && ($item['sys_val'] == ($item['wx_val'] - $item['refund_wx_val']))){
                $item['status'] = '成功';
            }

            if((input('param.status') == $item['status']) || input('param.status') == ''){
                $table[] = $item;
            }
        }

        $result['data'] = $table;
        $result['status'] = true;
        return $result;
    }

    // 账单对账
    private function getSysDsBill($date_re, $status)
    {
        $sql = "select tmp_x.x, ifnull(sum(b.jfje_),0) as sys_val,count(b.id_) as sys_num from
            (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_re['data']['num'].") tmp_x
            left join (
                select DISTINCT a.id_, a.jfje_, a.time_jf_ from zlzhsf.t_pay_order a, zlzhsf.t_pays_order_detail b, zlzhsf.psfxm c 
                where a.id_ = b.t_pays_order_id_ and b.sfxmdm_ = c.sfxmdm and c.sfxmlxdm in ('09')
                and is_qrzf_ = 1
                and UNIX_TIMESTAMP(time_jf_) > " .$date_re['data']['start']. "
                and UNIX_TIMESTAMP(time_jf_) < " .$date_re['data']['end'].  "
            ) b on tmp_x.x = (( cast(UNIX_TIMESTAMP(b.time_jf_) as signed) - ".$date_re['data']['start'].") div (".$date_re['data']['section']."))
            group by tmp_x.x
        ";

        $model = new OrderModel();
        return $model->query($sql);
    }

    // 账单对账
    private function getWxDsBill($date_re, $status){
        $sql = "select tmp_x.x, ifnull(sum(b.total_amount),0) as wx_val,count(b.order_sn_wx) as wx_num from
            (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_re['data']['num'].") tmp_x
            left join (
                select a.order_sn_wx, total_amount, pay_time from `".config('database.prefix')."wx_ds_bill` a 
                where pay_status = 'SUCCESS'
                and UNIX_TIMESTAMP(pay_time) > " .$date_re['data']['start'].  "
                and UNIX_TIMESTAMP(pay_time) < " .$date_re['data']['end'].  "
            ) b on tmp_x.x = (( cast(UNIX_TIMESTAMP(b.pay_time) as signed) - ".$date_re['data']['start'].") div (".$date_re['data']['section'].")) 
            group by tmp_x.x
        ";

        $model = new OrderModel();
        return $model->query($sql);
    }

    // 账单对账
    private function getRefundWxDsBill($date_re, $status){
        $sql = "select tmp_x.x, ifnull(sum(b.total_amount),0) as wx_val,count(b.order_sn_wx) as wx_num from
            (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_re['data']['num'].") tmp_x
            left join (
                select a.order_sn_wx, total_amount, pay_time from `".config('database.prefix')."wx_ds_bill` a join
                (SELECT order_sn_wx FROM `".config('database.prefix')."wx_ds_bill` WHERE pay_status = 'REFUND') b on a.order_sn_wx = b.order_sn_wx  
                where pay_status = 'SUCCESS'
                and UNIX_TIMESTAMP(pay_time) > " .$date_re['data']['start'].  "
                and UNIX_TIMESTAMP(pay_time) < " .$date_re['data']['end'].  "
            ) b on tmp_x.x = (( cast(UNIX_TIMESTAMP(b.pay_time) as signed) - ".$date_re['data']['start'].") div (".$date_re['data']['section'].")) 
            group by tmp_x.x
        ";

        $model = new OrderModel();
        return $model->query($sql);
    }

    // 账单对账
    private function getSysFdsBill($date_re, $status)
    {
        $sql = "select tmp_x.x, ifnull(sum(b.jfje_),0) as sys_val,count(b.id_) as sys_num from
            (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_re['data']['num'].") tmp_x
            left join (
                select DISTINCT a.id_, a.jfje_, a.time_jf_ from zlzhsf.t_pay_order a, zlzhsf.t_pays_order_detail b, zlzhsf.psfxm c 
                where a.id_ = b.t_pays_order_id_ and b.sfxmdm_ = c.sfxmdm and c.sfxmlxdm in ('01', '02', '03')
                and is_qrzf_ = 1
                and UNIX_TIMESTAMP(time_jf_) > " .$date_re['data']['start'].  "
                and UNIX_TIMESTAMP(time_jf_) < " .$date_re['data']['end'].  "
            ) b on tmp_x.x = (( cast(UNIX_TIMESTAMP(b.time_jf_) as signed) - ".$date_re['data']['start'].") div (".$date_re['data']['section']."))
            group by tmp_x.x
        ";

        $model = new OrderModel();
        return $model->query($sql);
    }

    // 账单对账
    private function getWxFdsBill($date_re, $status){
        $sql = "select tmp_x.x, ifnull(sum(b.total_amount),0) as wx_val,count(b.order_sn_wx) as wx_num from
            (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_re['data']['num'].") tmp_x
            left join (
                select a.order_sn_wx, total_amount, pay_time from `".config('database.prefix')."wx_fds_bill` a  
                where pay_status = 'SUCCESS'
                and UNIX_TIMESTAMP(pay_time) > " .$date_re['data']['start'].  "
                and UNIX_TIMESTAMP(pay_time) < " .$date_re['data']['end'].  "
            ) b on tmp_x.x = (( cast(UNIX_TIMESTAMP(b.pay_time) as signed) - ".$date_re['data']['start'].") div (".$date_re['data']['section'].")) 
            group by tmp_x.x
        ";

        $model = new OrderModel();
        return $model->query($sql);
    }

    // 账单对账
    private function getRefundWxFdsBill($date_re, $status){
        $sql = "select tmp_x.x, ifnull(sum(b.total_amount),0) as wx_val,count(b.order_sn_wx) as wx_num from
            (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_re['data']['num'].") tmp_x
            left join (
                select a.order_sn_wx, total_amount, pay_time from `".config('database.prefix')."wx_fds_bill` a join
                (SELECT order_sn_wx FROM `".config('database.prefix')."wx_fds_bill` WHERE pay_status = 'REFUND') b on a.order_sn_wx = b.order_sn_wx  
                where pay_status = 'SUCCESS'
                and UNIX_TIMESTAMP(pay_time) > " .$date_re['data']['start'].  "
                and UNIX_TIMESTAMP(pay_time) < " .$date_re['data']['end'].  "
            ) b on tmp_x.x = (( cast(UNIX_TIMESTAMP(b.pay_time) as signed) - ".$date_re['data']['start'].") div (".$date_re['data']['section'].")) 
            group by tmp_x.x
        ";

        $model = new OrderModel();
        return $model->query($sql);
    }
    
    //设备收入汇总表
    private function deviceMark(){
        $result = [
            'status' => false,
            'data'   => [],
            'msg'    => '',
        ];

        $date_re = $this->getDate();
        if(!$date_re['status']){
            return $date_re;
        }
        
        $sql = "select c.`name`, e.`name` as cat_name, d.`name` as brand_name, f.`name` as type_name, c.sub_id, c.area_id, count(a.`id`) as pay_num, sum(-a.money - a.gift) as pay_val  
            from zl_balance a, zl_bill_payments b, zl_device c, zl_brand d, zl_device_cat e, zl_device_type f, zl_device_log g
            where a.source_id = b.trade_no and g.device_id = c.`id` and c.brand_id = d.`id` and c.cat_id = e.`id` and c.type_id = f.`id`and a.source_id = g.bill_id 
            and a.type = 1
            and a.ctime > " .$date_re['data']['start'].  "
            and a.ctime < " .$date_re['data']['end'].  "
            group by g.device_id 
            order by pay_val desc";

        $model = new BalanceModel();
        $re = $model->query($sql);

        foreach ( $re as $key => $val ) {
            $re[$key]['area'] = get_area($val['area_id']); 
        } 

        $result['data'] = $re;
        $result['status'] = true;
        return $result;
    }

    //用户支出汇总表
    private function userMark(){
        $result = [
            'status' => false,
            'data'   => [],
            'msg'    => '',
        ];

        $mobile = input('param.mobile');
        $date_re = $this->getDate();
        if(!$date_re['status']){
            return $date_re;
        }
        
        $sql = "select c.mobile, c.nickname, c.balance, c.bindingbalance, count(a.id) as pay_num, sum(-a.money - a.gift) as pay_val  
            from zl_balance a, zl_bill_payments b, zl_user c
            where a.source_id = b.trade_no and b.user_id = c.id 
            and a.type = 1
            and c.mobile like '%".$mobile."%'
            and a.ctime > " .$date_re['data']['start'].  "
            and a.ctime < " .$date_re['data']['end'].  "
            group by c.id
            order by pay_val desc";

        $model = new BalanceModel();
        $re = $model->query($sql);

        $result['data'] = $re;
        $result['status'] = true;
        return $result;
    }

    //资金管理汇总表
    private function balanceMark($type, $date_arr, $where, $join_val){
        $result = [
            'status' => false,
            'data'   => [],
            'msg'    => '',
        ];

        $sql = "";
        if($type == "balance_pay") {
            $sql = "select tmp_x.x,ifnull(sum(-b.money - b.gift),0) as val,count(b.id) as num ";
        } else if($type == "balance_recharge") {
            $sql = "select tmp_x.x,ifnull(sum(b.money),0) as val,count(b.id) as num ";
        } else if($type == "balance_recharge_gift"){
            $sql = "select tmp_x.x,ifnull(sum(b.gift),0) as val,count(b.id) as num ";
        } else {
            $sql = "select tmp_x.x,ifnull(sum(b.money + b.gift),0) as val,count(b.id) as num ";
        }
 
        $sql = $sql . " from
              (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_arr['num'].") tmp_x
            left join (
                select * from `".config('database.prefix')."balance`
                where 1=1
                ".$where."
            ) b on tmp_x.x = (( cast(b.".$join_val." as signed) - ".$date_arr['start'].") div (".$date_arr['section']."))
            group by tmp_x.x
        ";

        $model = new OrderModel();
        $re = $model->query($sql);

        $result['data'] = $re;
        $result['status'] = true;
        return $result;
    }

    //订单报表
    private function orderMark($date_arr,$where,$join_val){
        $result = [
            'status' => false,
            'data'   => [],
            'msg'    => '',
        ];

        $sql = "
            select tmp_x.x,ifnull(sum(o.order_amount),0) as val,count(o.order_id) as num
            from
              (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_arr['num'].") tmp_x
            left join (
                select * from `".config('database.prefix')."order`
                where 1=1
                ".$where."
            ) o on tmp_x.x = (( cast(o.".$join_val." as signed) - ".$date_arr['start'].") div (".$date_arr['section']."))
            group by tmp_x.x
        ";

        $model = new OrderModel();
        $re = $model->query($sql);

        $result['data'] = $re;
        $result['sql'] = $sql;
        $result['status'] = true;
        return $result;
    }

    //支付单报表
    private function paymentsMark($date_arr,$where,$join_val){
        $result = [
            'status' => false,
            'data'   => [],
            'msg'    => '',
        ];

        $sql = "
            select tmp_x.x,ifnull(sum(o.money),0) as val,count(o.payment_id) as num
            from
              (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_arr['num'].") tmp_x
            left join (
                select * from `".config('database.prefix')."bill_payments`
                where 1=1
                ".$where."
            ) o on tmp_x.x = (( cast(o.".$join_val." as signed)  - ".$date_arr['start'].") div (".$date_arr['section']."))
            group by tmp_x.x
        ";
        $model = new OrderModel();
        $re = $model->query($sql);
        $result['data'] = $re;
        $result['sql'] = $sql;
        $result['status'] = true;
        return $result;
    }
    //退款单报表
    private function refundMark($date_arr,$where,$join_val){
        $result = [
            'status' => false,
            'data'   => [],
            'msg'    => '',
        ];

        $sql = "
            select tmp_x.x,ifnull(sum(o.money),0) as val,count(o.refund_id) as num
            from
              (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_arr['num'].") tmp_x
            left join (
                select * from `".config('database.prefix')."bill_refund`
                where 1=1
                ".$where."
            ) o on tmp_x.x = (( cast(o.".$join_val." as signed) - ".$date_arr['start'].") div (".$date_arr['section']."))
            group by tmp_x.x
        ";
        $model = new OrderModel();
        $re = $model->query($sql);
        $result['data'] = $re;
        $result['sql'] = $sql;
        $result['status'] = true;
        return $result;
    }

    //用户提现报表
    private function tocashMark($date_arr,$where,$join_val){
        $result = [
            'status' => false,
            'data'   => [],
            'msg'    => '',
        ];

        $sql = "
            select tmp_x.x,ifnull(sum(o.money),0) as val,count(o.id) as num
            from
              (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_arr['num'].") tmp_x
            left join (
                select * from `".config('database.prefix')."user_tocash`
                where 1=1
                ".$where."
            ) o on tmp_x.x = ((cast(o.".$join_val." as signed) - ".$date_arr['start'].") div (".$date_arr['section']."))
            group by tmp_x.x
        ";
        $model = new OrderModel();
        $re = $model->query($sql);
        $result['data'] = $re;
        $result['sql'] = $sql;
        $result['status'] = true;
        return $result;
    }

    //根据时间节点和时间粒度算x轴个数
    private function getTmp($date_arr){
        $result = [
            'status' => false,
            'data'   => $date_arr,
            'msg'    => '',
        ];
        if($date_arr['end'] <= $date_arr['start']){
            $result['msg']='开始时间必须小于等于结束时间';
            return $result;
        }

        $diff_day = $date_arr['end'] - $date_arr['start'];
        switch($date_arr['section']){
            case 1:                 //小时
                $result['data']['section'] = 60*60;
                break;
            case 2:                 //天
                $result['data']['section'] = 60*60*24;
                break;
            default:
                $result['msg'] = '没有此时间粒度';
                return $result;
        }
        //算x轴数据个数
        $result['data']['num'] = ceil($diff_day / $result['data']['section']);

        $result['status'] = true;
        return $result;

    }

    private function getXdata($date_arr){
        $result = [
            'status' => true,
            'data'   => [],
            'msg'    => '',
        ];


        //校验,x轴最多1000个
        $x_nums = ceil(($date_arr['end'] - $date_arr['start']) / $date_arr['section']);
        if($x_nums > 1000){
            return error_code(13226);
        }



        $x_type = "";
        switch($date_arr['section']){
            case 3600:                 //小时
                if($x_nums<= 24) {
                    $x_type = "H时";
                }elseif($x_nums <= 720){
                    $x_type = "d日H时";
                }else{
                    $x_type = "m月d日H时";
                }
                break;
            case 86400:                 //天
                $x_type = "Y-m-d";
                // if($x_nums <= 31){
                //     $x_type = "d号";
                // }elseif($x_nums <= 365){
                //     $x_type = "m.d";
                // }else{
                //     $x_type = "Y.m.d";
                // }
                break;
        }
        if($x_type == ""){
            return error_code(10000);
        }

        for($i = $date_arr['start'];$i<($date_arr['end']);$i=$i+$date_arr['section']){
            if($date_arr['section'] == 3600){
                $result['data'][] = date($x_type,$i+$date_arr['section']);      //小时是结尾的表示当前切片的时间
            }else{
                $result['data'][] = date($x_type,$i);       //天是开始表示当前切片的时间
            }

        }
        return $result;
    }
 
    /*
     * 对账信息
     * */
    public function billingds()
    {
        $this->view->engine->layout(false);
        $dates        = input('dates/s', '');
        $this->assign('dates', $dates);
        return $this->fetch();
    }

    /*
     * 对账信息
     * */
    public function billingfds()
    {
        $this->view->engine->layout(false);
        $dates        = input('dates/s', '');
        $this->assign('dates', $dates);
        return $this->fetch();
    }

    public function dobillingds()
    {
        $result = [
            'status' => true,
            'data' => '',
            'msg' => ''
        ];

        if(!input('param.date')){
            return error_code(10051);
        }
        if(!input('?param.index')){
            return error_code(10051);
        }

        $wxDsBillModel = new WxDsBillModel;
        $wxDsBillModel->dobilling(input('param.date'));

        $result['data']['date'] = input('param.date');
        $result['data']['index'] = input('param.index') + 1;

        return $result;
    }

    public function dobillingfds()
    {
        $result = [
            'status' => true,
            'data' => '',
            'msg' => ''
        ];

        if(!input('param.date')){
            return error_code(10051);
        }
        if(!input('?param.index')){
            return error_code(10051);
        }

        $wxFdsBillModel = new WxFdsBillModel;
        $wxFdsBillModel->dobilling(input('param.date'));

        $result['data']['date'] = input('param.date');
        $result['data']['index'] = input('param.index') + 1;

        return $result;
    }

    // 获取新增与日活，无实时信息，数据延迟一天
    public function dailyretain (){
        if(Request::isAJAX()){
            $date_re = $this->getDate();
            if(!$date_re['status']){
                return $date_re;
            }

            $this->option['title']['text'] = "新增&日活统计表";
            $this->option['yAxis']['name'] = "人";
            $legend = [
                'uv_new' => '新增',
                'uv' => '日活',
            ];
            
            $this->option['legend']['data'] = array_values($legend);
            
            // 取时间范围，不包含今天。
            $xData = $this->getXdata($date_re['data'], false);
            if(!$xData['status']){
                return $xData;
            }

            $this->option['xAxis']['data'] = $xData['data'];
            $datas = [];        //保存所有数据

            foreach($legend as $k => $v){
                switch($k){
                    case 'uv_new':
                        $where = " and UNIX_TIMESTAMP(refDate) >=" .$date_re['data']['start'].  "
                            and UNIX_TIMESTAMP(refDate) <=" .$date_re['data']['end'];

                        $data = $this->dailyretainUvNewMark($k, $date_re['data'], $where, 'refDate');
                        break;
                    case 'uv':
                        $where = " and UNIX_TIMESTAMP(refDate) >=" .$date_re['data']['start'].  "
                            and UNIX_TIMESTAMP(refDate) <=" .$date_re['data']['end'];

                        $data = $this->dailyretainUvNewMark($k, $date_re['data'], $where, 'refDate');
                        break;
                }

                if($data['status']){
                    $this->option['series'][] = [
                        'name' => $v,
                        'type' => 'line',
                        'data' => array_column($data['data'],'val')
                    ];
                    //存所有数据
                    $datas[$k] = $data['data']; 
                }else{
                    $this->option['series'][] = [
                        'name' => $v,
                        'type' => 'line',
                        'data' => []
                    ];
                }
            }
            
            //组装数据列表用于table里使用
            $table = [];
            for($i = 0;$i< $date_re['data']['num'];$i++){
                $item = [];
                $item['x'] = $xData['data'][$i];
                foreach($datas as $k => $v){
                    $item[$k.'_val'] = $v[$i]['val'];
                }
                $table[] = $item;
            }

            return [
                'status' => true,
                'data'   => [
                    'option' => $this->option,
                    'table' => $table
                ],
                'msg'    => '',
            ];
        }else{
            return $this->fetch();
        }
    }

    // 新增
    private function dailyretainUvNewMark($type, $date_arr, $where, $join_val){
        $result = [
            'status' => false,
            'data'   => [],
            'msg'    => '',
        ];

        $sql = "";
        if($type == "uv_new") {
            $sql = "select x, ifnull(sum(visitUvNew),0) as val ";
        } else if($type == "uv") {
            $sql = "select x, ifnull(sum(visitUv),0) as val ";
        }
 
        $sql = $sql . " from
              (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_arr['num'].") tmp_x
            left join (
                select * from `".config('database.prefix')."wx_daily_retain`
                where 1=1
                ".$where."
            ) b on tmp_x.x = (( cast( UNIX_TIMESTAMP(b.".$join_val.") as signed) - ".$date_arr['start'].") div (".$date_arr['section']."))
            group by tmp_x.x
        ";

        $model = new WxDailyRetainModel();
        $re = $model->query($sql);

        $result['data'] = $re;
        $result['status'] = true;
        return $result;
    } 

}
